package dao

import (
	"bookmark-web/models"
	"errors"
	"log"
	"strings"
	"time"
)

// CreateCategory 创建分类
func CreateCategory(category *models.Category) error {
	// INSERT INTO categories (name, parent_id, level, path, user_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW())
	return DB.Create(category).Error
}

// GetCategoryByID 根据ID获取分类
func GetCategoryByID(id uint, userID uint) (models.Category, error) {
	var category models.Category
	// SELECT * FROM categories WHERE id = ? AND user_id = ? LIMIT 1
	err := DB.Where("id = ? AND user_id = ?", id, userID).
		Preload("Parent").
		Preload("Children").
		First(&category).Error
	return category, err
}

// GetCategoriesByUserID 根据用户ID获取分类列表（分页）
func GetCategoriesByUserID(userID uint, page, pageSize int, search string) ([]*models.Category, int64, error) {
	var categories []*models.Category
	var total int64

	// SELECT * FROM categories WHERE user_id = ?
	query := DB.Model(&models.Category{}).Where("user_id = ?", userID)

	// 如果有搜索关键词，添加模糊搜索
	// SELECT * FROM categories WHERE user_id = ? AND name LIKE '%?%'
	if search != "" {
		query = query.Where("name LIKE ?", "%"+search+"%")
	}

	// 获取总数
	// SELECT count(*) FROM categories WHERE user_id = ?
	err := query.Count(&total).Error
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	// SELECT * FROM categories WHERE user_id = ? ORDER BY level ASC, created_at ASC LIMIT ? OFFSET ?
	offset := (page - 1) * pageSize
	err = query.Preload("Parent").
		Preload("Children").
		Offset(offset).
		Limit(pageSize).
		Order("level ASC, created_at ASC").
		Find(&categories).Error

	return categories, total, err
}

// GetCategoriesByParentID 根据父分类ID获取子分类
func GetCategoriesByParentID(parentID *uint, userID uint) ([]*models.Category, error) {
	var categories []*models.Category
	// SELECT * FROM categories WHERE user_id = ?
	query := DB.Where("user_id = ?", userID)

	if parentID == nil {
		// 获取顶级分类
		// SELECT * FROM categories WHERE user_id = ? AND parent_id IS NULL
		query = query.Where("parent_id IS NULL")
	} else {
		// 获取指定父分类的子分类
		// SELECT * FROM categories WHERE user_id = ? AND parent_id = ?
		query = query.Where("parent_id = ?", *parentID)
	}

	// SELECT * FROM categories WHERE user_id = ? AND parent_id ... ORDER BY created_at ASC
	err := query.Preload("Children").
		Order("created_at ASC").
		Find(&categories).Error

	return categories, err
}

// GetCategoryTree 获取分类树（递归获取所有层级）
func GetCategoryTree(userID uint) ([]*models.Category, error) {
	var categories []models.Category

	// 先获取所有分类
	// SELECT * FROM categories WHERE user_id = ? ORDER BY level ASC, created_at ASC
	err := DB.Where("user_id = ?", userID).
		Order("level ASC, created_at ASC").
		Find(&categories).Error
	if err != nil {
		return nil, err
	}

	// 构建分类树
	categoryMap := make(map[uint]*models.Category)
	var rootCategories []*models.Category

	// 第一遍：创建映射
	for i := range categories {
		categoryMap[categories[i].ID] = &categories[i]
		categories[i].Children = []*models.Category{}
	}

	// 第二遍：构建树形结构
	for i := range categories {
		if categories[i].ParentID == nil {
			// 顶级分类
			rootCategories = append(rootCategories, &categories[i])
		} else {
			// 子分类，添加到父分类的Children中
			if parent, exists := categoryMap[*categories[i].ParentID]; exists {
				parent.Children = append(parent.Children, &categories[i])
			}
		}
	}

	return rootCategories, nil
}

// UpdateCategory 更新分类
func UpdateCategory(category *models.Category) error {
	// UPDATE categories SET name = ?, parent_id = ?, level = ?, path = ?, user_id = ?, updated_at = NOW() WHERE id = ?
	log.Printf("准备更新分类 - ID: %d, Name: %s, ParentID: %v, Path: %s", category.ID, category.Name, category.ParentID, category.Path)

	// 明确指定要更新的字段
	//result := DB.Model(&models.Category{}).Where("id = ?", category.ID).Select("name", "parent_id", "level", "path", "updated_at").
	//	Updates(map[string]interface{}{
	//		"name":       category.Name,
	//		"parent_id":  category.ParentID,
	//		"level":      category.Level,
	//		"path":       category.Path,
	//		"updated_at": time.Now(),
	//	})
	updateFields := map[string]interface{}{
		"name":       category.Name,
		"level":      category.Level,
		"path":       category.Path,
		"updated_at": time.Now(),
	}

	// 处理parent_id，当为0时保存为NULL
	if category.ParentID != nil && *category.ParentID != 0 {
		updateFields["parent_id"] = category.ParentID
	} else {
		updateFields["parent_id"] = nil
	}

	result := DB.Model(&models.Category{}).Where("id = ?", category.ID).Select("name", "parent_id", "level", "path", "updated_at").
		Updates(updateFields)

	log.Printf("更新结果 - Rows affected: %d, Error: %v", result.RowsAffected, result.Error)
	return result.Error
}

// CheckCategoryChildrenExists 检查分类下是否有子分类
func CheckCategoryChildrenExists(id uint, userID uint) (bool, error) {
	// SELECT count(*) FROM categories WHERE parent_id = ? AND user_id = ?
	var count int64
	err := DB.Model(&models.Category{}).Where("parent_id = ? AND user_id = ?", id, userID).Count(&count).Error
	return count > 0, err
}

// DeleteCategory 删除分类（级联删除子分类）
func DeleteCategory(id uint, userID uint) error {
	// 先检查分类是否存在且属于该用户
	// SELECT * FROM categories WHERE id = ? AND user_id = ? LIMIT 1
	var category models.Category
	err := DB.Where("id = ? AND user_id = ?", id, userID).First(&category).Error
	if err != nil {
		return err
	}

	// 递归删除所有子分类
	return deleteWithChildren(id, userID)
}

// deleteWithChildren 递归删除分类及其所有子分类
func deleteWithChildren(categoryID uint, userID uint) error {
	// 先获取所有子分类
	// SELECT * FROM categories WHERE parent_id = ? AND user_id = ?
	var children []models.Category
	err := DB.Where("parent_id = ? AND user_id = ?", categoryID, userID).Find(&children).Error
	if err != nil {
		return err
	}

	var count int64
	err = DB.Model(&models.Bookmark{}).Where("category_id = ? AND user_id = ?", categoryID, userID).Count(&count).Error
	if err != nil {
		return err
	}
	if count > 0 {
		return errors.New("当前分类或其子分类下有网址关联，请先删除网址")
	}

	// 递归删除子分类
	for _, child := range children {
		err = deleteWithChildren(child.ID, userID)
		if err != nil {
			return err
		}
	}

	// 删除当前分类
	// DELETE FROM categories WHERE id = ? AND user_id = ?
	return DB.Where("id = ? AND user_id = ?", categoryID, userID).Delete(&models.Category{}).Error
}

// CheckCategoryExists 检查分类名称是否已存在（同一用户下同一父分类下不能有重名）
func CheckCategoryExists(name string, parentID *uint, userID uint, excludeID ...uint) (bool, error) {
	// SELECT count(*) FROM categories WHERE name = ? AND user_id = ?
	query := DB.Model(&models.Category{}).Where("name = ? AND user_id = ?", name, userID)

	if parentID == nil {
		// SELECT count(*) FROM categories WHERE name = ? AND user_id = ? AND parent_id IS NULL
		// 顶级分类修改名称/某一分类修改为顶级分类
		query = query.Where("parent_id IS NULL")
	} else {
		// SELECT count(*) FROM categories WHERE name = ? AND user_id = ? AND parent_id = ?
		// 修改子分类名称/某一分类修改为其它分类的子分类
		query = query.Where("parent_id = ?", *parentID)
	}

	// 如果是更新操作，排除当前分类ID，允许修改为原本的名字，即只修改分类描述（但此项目没有发呢类描述）
	// 如果是修改所属分类，排除当前分类ID，允许修改为原本的分类
	// SELECT count(*) FROM categories WHERE name = ? AND user_id = ? AND parent_id ... AND id != ?
	if len(excludeID) > 0 {
		query = query.Where("id != ?", excludeID[0])
	}

	var count int64
	err := query.Count(&count).Error
	return count > 0, err
}

// BuildCategoryPath 构建分类路径
//
//	func BuildCategoryPath(categoryID uint, parentID uint) (string, error) {
//		var paths []string
//		currentID := categoryID
//
//		for currentID != 0 {
//			// SELECT id, name, parent_id FROM categories WHERE id = ?
//			var category models.Category
//			err := DB.Select("id, name, parent_id").Where("id = ?", currentID).First(&category).Error
//			if err != nil {
//				return "", err
//			}
//
//			paths = append([]string{category.Name}, paths...)
//
//			if category.ParentID == nil {
//				break
//			}
//			// 防止当前分类的父级ID修改后，从数据库中查询出的父级ID不正确
//			if *category.ParentID != parentID {
//				*category.ParentID = parentID
//			}
//			currentID = *category.ParentID
//		}
//
//		return strings.Join(paths, "/"), nil
//	}
func BuildCategoryPath(category models.Category) (string, error) {
	var paths []string
	currentID := category.ID
	// 从当前分类开始，由下之上迭代，构建分类路径
	for currentID != 0 {
		// 存储当前分类名称
		paths = append([]string{category.Name}, paths...)
		// 如果无父级分类，则结束循环
		if category.ParentID == nil {
			break
		}
		// 存在父级分类，则更新 currentID
		currentID = *category.ParentID
		// 查询出父级分类的信息
		err := DB.Raw("SELECT id, name, parent_id FROM categories WHERE id = ?", currentID).Scan(&category).Error
		if err != nil {
			return "", err
		}
	}
	// 构建分类路径并返回
	return strings.Join(paths, "/"), nil
}

// GetCategoryLevel 获取分类层级
func GetCategoryLevel(parentID *uint) int {
	if parentID == nil {
		return 1
	}

	// SELECT level FROM categories WHERE id = ?
	var parent models.Category
	err := DB.Select("level").Where("id = ?", *parentID).First(&parent).Error
	if err != nil {
		return 1
	}

	return parent.Level + 1
}

// SearchCategories 搜索分类（模糊匹配分类名称）
func SearchCategories(userID uint, keyword string, page, pageSize int) ([]*models.Category, int64, error) {
	var categories []*models.Category
	var total int64

	// SELECT * FROM categories WHERE user_id = ? AND name LIKE '%?%'
	query := DB.Model(&models.Category{}).Where("user_id = ? AND name LIKE ?", userID, "%"+keyword+"%")

	// 获取总数
	// SELECT count(*) FROM categories WHERE user_id = ? AND name LIKE '%?%'
	err := query.Count(&total).Error
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	// SELECT * FROM categories WHERE user_id = ? AND name LIKE '%?%' ORDER BY level ASC, created_at ASC LIMIT ? OFFSET ?
	offset := (page - 1) * pageSize
	err = query.Preload("Parent").
		Offset(offset).
		Limit(pageSize).
		Order("level ASC, created_at ASC").
		Find(&categories).Error

	return categories, total, err
}
